﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ExportToDWGTool
{
    //设备
    class GDSBInfo  
    {
        public string BSM;//标识码
        public double lng;//经度
        public double lat;//纬度
        public string strMC;//名称
        public double dj;//档距
        public string strLY; //图层及块参照名称
        public string strLineTp;//导线类型
        public string strLineType;//连接方式
        public string strCZ;//材质
        public string strXZ;//性质
    }
    //线关联点
    class GDXDInfo
    {
        public string BSM;//标识码
        public double lng;//经度
        public double lat;//纬度
        public string strMC;//名称
        public int xh;//序号
        public string xID;//线ID
    }

    //线
    class GDXInfo
    {
        public string BSM;//标识码
        public double fCD;//长度

        public string strMC;//名称

        public string strTQ;//台区

        public int SLX;//起点类型
        public int ELX;//终点类型

        public Dictionary<int, GDXDInfo> dic;
    }

    class ReadDataFromSQL
    {       
        public Dictionary<string, GDSBInfo> m_DicSB = new Dictionary<string, GDSBInfo>();
        public Dictionary<string, GDXInfo> m_DicX = new Dictionary<string, GDXInfo>();

        /// <summary> 连接服务器</summary>
        public SqlConnection sqlConnection;

        public ReadDataFromSQL(string strIP,string strDB,string strUID,string strPWD)
        {
            try
            {
                string conString = string.Format( @"Server={0};DataBase={1};uid={2};pwd={3};MultipleActiveResultSets=true", strIP, strDB, strUID, strPWD);

                sqlConnection = new SqlConnection(conString);
                sqlConnection.Open();
            }
            catch (InvalidOperationException e)
            {
                Debug.WriteLine(e.ToString());
            }
            catch (SqlException e)
            {
                Debug.WriteLine(e.Message);
            }
        }

        ~ReadDataFromSQL()
        {
            try
            {
                if (sqlConnection != null)
                {
                    sqlConnection.Close();
                    sqlConnection = null;
                }
            }
            catch
            {
            }
        }

        public void GetDataFromTable()
        {
            GetDLXData();
            GetDXData();

            GetTGSBData();

            GetBYQSBData();

            GetPDGSBData();

            GetDYFZXSBData();

            GetQZJSBData();

            GetYHJRDSBData();

            GetJLXSBData();


        }

        public void GetDLXData()
        {
           
            try
            {
                //定义查询语句
                String sql = "select * from DLD";
                SqlCommand sqlComm = new SqlCommand(sql, sqlConnection);
                //接收查询到的sql数据
                SqlDataReader reader = sqlComm.ExecuteReader();
                //读取数据
                while (reader.Read())
                {
                    string strBSM = reader["UUID"].ToString();
                    string strMC = reader["NAME"].ToString();

                    double fCD = (double)reader["CHANGDU"];

                    string strTask = reader["ABOUTTASK"].ToString();

                    GDXInfo pInfo = new GDXInfo();
                    pInfo.BSM = strBSM;
                    pInfo.fCD = fCD;
                    pInfo.strMC = strMC;
                    pInfo.strTQ = GetTQMC(strTask);

                    pInfo.dic = new Dictionary<int, GDXDInfo>();

                    GetDXDData(strBSM, pInfo.dic);

                    //m_DicX.Add(strBSM, pInfo);

                }
                reader.Close();
              
            }
            catch(Exception ex)
            {
               // sqlConnection.Close();
            }

           // sqlConnection.Close();
        }

        public void GetDXData()
        {
           // sqlConnection.Open();
            try
            {
                //定义查询语句
                String sql = "select * from WireLine";
                SqlCommand sqlComm = new SqlCommand(sql, sqlConnection);
                //接收查询到的sql数据
                SqlDataReader reader = sqlComm.ExecuteReader();
                //读取数据
                while (reader.Read())
                {
                    string strBSM = reader["UUID"].ToString();
                    string strMC = reader["NAME"].ToString();

                    double fCD = double.Parse(reader["CHANGDU"].ToString());

                    string strTask = reader["ABOUTTASK"].ToString();

                    GDXInfo pInfo = new GDXInfo();
                    pInfo.BSM = strBSM;
                    pInfo.fCD = fCD;
                    pInfo.strMC = strMC;
                    pInfo.strTQ = GetTQMC(strTask);

                    pInfo.dic = new Dictionary<int, GDXDInfo>();

                    GetDXDData(strBSM, pInfo.dic);

                    m_DicX.Add(strBSM, pInfo);

                }
                reader.Close();

            }
            catch (Exception ex)
            {
               // sqlConnection.Close();
            }

            //sqlConnection.Close();
        }

        public void GetDXDData(string strBSM, Dictionary<int, GDXDInfo> dic)
        {
            try
            {
                //定义查询语句
                String sql = "select * from LINEGL where LINEID ='"+ strBSM + "'";
                SqlCommand sqlComm = new SqlCommand(sql, sqlConnection);
                //接收查询到的sql数据
                SqlDataReader reader = sqlComm.ExecuteReader();
                //读取数据
                while (reader.Read())
                {
                    string BSM = reader["UUID"].ToString();
                    string strMC = reader["XUHAO"].ToString();

                    double lng = double.Parse(reader["JINGDU"].ToString());
                    double lat = double.Parse(reader["WEIDU"].ToString());
                    int xh = int.Parse(reader["XUHAO"].ToString());

                    GDXDInfo pInfo = new GDXDInfo();
                    pInfo.BSM = strBSM;
                    pInfo.strMC = strMC;
                    pInfo.lng = lng;
                    pInfo.lat = lat;
                    pInfo.xh = xh;

                    dic.Add(xh,pInfo);
                }
                reader.Close();

            }
            catch (Exception ex)
            {
               
            }

        }

        public string GetTQMC(string strBSM)
        {
            string strMC = "";
            try
            {
                //定义查询语句
                String sql = "select * from TASK where UUID ='" + strBSM + "'";
                SqlCommand sqlComm = new SqlCommand(sql, sqlConnection);
                //接收查询到的sql数据
                SqlDataReader reader = sqlComm.ExecuteReader();
                //读取数据
                while (reader.Read())
                {
                   // string BSM = reader["UUID"].ToString();
                    strMC = reader["NAME"].ToString();
                    break;                 
                }
                reader.Close();

            }
            catch (Exception ex)
            {

            }
            return strMC;
        }

        public void GetTGSBData()
        {
           // sqlConnection.Open();
            try
            {
                //定义查询语句
                String sql = "select * from SB_GT";
                SqlCommand sqlComm = new SqlCommand(sql, sqlConnection);
                //接收查询到的sql数据
                SqlDataReader reader = sqlComm.ExecuteReader();
                //读取数据
                while (reader.Read())
                {
                    string strBSM = reader["UUID"].ToString();
                    string strMC = reader["NAME"].ToString();

                    double lng = double.Parse(reader["JINGDU"].ToString());
                    double lat = double.Parse(reader["WEIDU"].ToString());

                    GDSBInfo pInfo = new GDSBInfo();
                    pInfo.BSM = strBSM;
                    pInfo.strMC = strMC;
                    pInfo.lng = lng;
                    pInfo.lat = lat;
                    pInfo.strLY = "GDGT";

                    m_DicSB.Add(strBSM, pInfo);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                //sqlConnection.Close();
            }

            //sqlConnection.Close();
        }

        public void GetBYQSBData()
        {
           // sqlConnection.Open();
            try
            {
                //定义查询语句
                String sql = "select * from SB_BYQ";
                SqlCommand sqlComm = new SqlCommand(sql, sqlConnection);
                //接收查询到的sql数据
                SqlDataReader reader = sqlComm.ExecuteReader();
                //读取数据
                while (reader.Read())
                {
                    string strBSM = reader["UUID"].ToString();
                    string strMC = reader["NAME"].ToString();

                    double lng = double.Parse(reader["JINGDU"].ToString());
                    double lat = double.Parse(reader["WEIDU"].ToString());

                    GDSBInfo pInfo = new GDSBInfo();
                    pInfo.BSM = strBSM;
                    pInfo.strMC = strMC;
                    pInfo.lng = lng;
                    pInfo.lat = lat;
                    pInfo.strLY = "GDBYQ";

                    m_DicSB.Add(strBSM, pInfo);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
               // sqlConnection.Close();
            }

           // sqlConnection.Close();
        }

        public void GetPDGSBData()
        {
            //sqlConnection.Open();
            try
            {
                //定义查询语句
                String sql = "select * from SB_PDG";
                SqlCommand sqlComm = new SqlCommand(sql, sqlConnection);
                //接收查询到的sql数据
                SqlDataReader reader = sqlComm.ExecuteReader();
                //读取数据
                while (reader.Read())
                {
                    string strBSM = reader["UUID"].ToString();
                    string strMC = reader["NAME"].ToString();

                    double lng = double.Parse(reader["JINGDU"].ToString());
                    double lat = double.Parse(reader["WEIDU"].ToString());

                    GDSBInfo pInfo = new GDSBInfo();
                    pInfo.BSM = strBSM;
                    pInfo.strMC = strMC;
                    pInfo.lng = lng;
                    pInfo.lat = lat;
                    pInfo.strLY = "GDPDG";

                    m_DicSB.Add(strBSM, pInfo);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
               // sqlConnection.Close();
            }

           // sqlConnection.Close();
        }

        public void GetDYFZXSBData()
        {
            //sqlConnection.Open();
            try
            {
                //定义查询语句
                String sql = "select * from SB_DYFZX";
                SqlCommand sqlComm = new SqlCommand(sql, sqlConnection);
                //接收查询到的sql数据
                SqlDataReader reader = sqlComm.ExecuteReader();
                //读取数据
                while (reader.Read())
                {
                    string strBSM = reader["UUID"].ToString();
                    string strMC = reader["NAME"].ToString();

                    double lng = double.Parse(reader["JINGDU"].ToString());
                    double lat = double.Parse(reader["WEIDU"].ToString());

                    GDSBInfo pInfo = new GDSBInfo();
                    pInfo.BSM = strBSM;
                    pInfo.strMC = strMC;
                    pInfo.lng = lng;
                    pInfo.lat = lat;
                    pInfo.strLY = "GDDYFZX";

                    m_DicSB.Add(strBSM, pInfo);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
               // sqlConnection.Close();
            }

            //sqlConnection.Close();
        }

        public void GetQZJSBData()
        {
            //sqlConnection.Open();
            try
            {
                //定义查询语句
                String sql = "select * from SB_QZJ";
                SqlCommand sqlComm = new SqlCommand(sql, sqlConnection);
                //接收查询到的sql数据
                SqlDataReader reader = sqlComm.ExecuteReader();
                //读取数据
                while (reader.Read())
                {
                    string strBSM = reader["UUID"].ToString();
                    string strMC = reader["NAME"].ToString();

                    double lng = double.Parse(reader["JINGDU"].ToString());
                    double lat = double.Parse(reader["WEIDU"].ToString());

                    GDSBInfo pInfo = new GDSBInfo();
                    pInfo.BSM = strBSM;
                    pInfo.strMC = strMC;
                    pInfo.lng = lng;
                    pInfo.lat = lat;
                    pInfo.strLY = "GDQZJ";

                    m_DicSB.Add(strBSM, pInfo);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                //sqlConnection.Close();
            }

            //sqlConnection.Close();
        }

        public void GetYHJRDSBData()
        {
           //sqlConnection.Open();
            try
            {
                //定义查询语句
                String sql = "select * from SB_YHJRD";
                SqlCommand sqlComm = new SqlCommand(sql, sqlConnection);
                //接收查询到的sql数据
                SqlDataReader reader = sqlComm.ExecuteReader();
                //读取数据
                while (reader.Read())
                {
                    string strBSM = reader["UUID"].ToString();
                    string strMC = reader["NAME"].ToString();

                    double lng = double.Parse(reader["JINGDU"].ToString());
                    double lat = double.Parse(reader["WEIDU"].ToString());

                    GDSBInfo pInfo = new GDSBInfo();
                    pInfo.BSM = strBSM;
                    pInfo.strMC = strMC;
                    pInfo.lng = lng;
                    pInfo.lat = lat;
                    pInfo.strLY = "GDYHJRD";

                    m_DicSB.Add(strBSM, pInfo);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                //sqlConnection.Close();
            }

           // sqlConnection.Close();
        }

        public void GetJLXSBData()
        {
           // sqlConnection.Open();
            try
            {
                //定义查询语句
                String sql = "select * from SB_JLX";
                SqlCommand sqlComm = new SqlCommand(sql, sqlConnection);
                //接收查询到的sql数据
                SqlDataReader reader = sqlComm.ExecuteReader();
                //读取数据
                while (reader.Read())
                {
                    string strBSM = reader["UUID"].ToString();
                    string strMC = reader["NAME"].ToString();

                    double lng = double.Parse(reader["JINGDU"].ToString());
                    double lat = double.Parse(reader["WEIDU"].ToString());

                    GDSBInfo pInfo = new GDSBInfo();
                    pInfo.BSM = strBSM;
                    pInfo.strMC = strMC;
                    pInfo.lng = lng;
                    pInfo.lat = lat;
                    pInfo.strLY = "GDJLX";

                    m_DicSB.Add(strBSM, pInfo);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
               // sqlConnection.Close();
            }

         //sqlConnection.Close();
        }

    }
}
